package com.zjcloud.academic.utils;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 学校信息表初始化工具类，用于在应用启动时检查表结构并创建必要的表
 */
@Component
public class SchoolInfoTableInitializer implements InitializingBean {

    private static final Logger logger = LoggerFactory.getLogger(SchoolInfoTableInitializer.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void afterPropertiesSet() throws Exception {
        try {
            // 检查表是否存在
            boolean tableExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'zjcloudtest' AND table_name = 'jwgl_school_info'",
                Integer.class) > 0;

            if (!tableExists) {
                logger.info("开始创建jwgl_school_info表...");
                // 创建表结构
                jdbcTemplate.execute(
                    "CREATE TABLE jwgl_school_info (" +
                    "    school_id bigint NOT NULL AUTO_INCREMENT," +
                    "    school_name varchar(255) NOT NULL," +
                    "    school_code varchar(50) NOT NULL," +
                    "    address varchar(255) DEFAULT NULL," +
                    "    contact_person varchar(100) DEFAULT NULL," +
                    "    contact_phone varchar(20) DEFAULT NULL," +
                    "    current_semester varchar(50) DEFAULT NULL," +
                    "    status varchar(1) DEFAULT '0'," +
                    "    create_by varchar(100) DEFAULT NULL," +
                    "    create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP," +
                    "    update_by varchar(100) DEFAULT NULL," +
                    "    update_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP," +
                    "    PRIMARY KEY (school_id)" +
                    ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4"
                );

                // 创建索引
                jdbcTemplate.execute("CREATE INDEX idx_school_name ON jwgl_school_info(school_name)");
                jdbcTemplate.execute("CREATE INDEX idx_school_code ON jwgl_school_info(school_code)");
                jdbcTemplate.execute("CREATE INDEX idx_status ON jwgl_school_info(status)");

                // 插入测试数据
                jdbcTemplate.execute(
                    "INSERT INTO jwgl_school_info (school_name, school_code, address, contact_person, contact_phone, status) " +
                    "VALUES " +
                    "('智教云示范中学', 'ZJYZX', '北京市海淀区中关村科技园', '张三', '13800138000', '0'), " +
                    "('智能技术学院', 'ZNJSC', '上海市浦东新区张江高科技园区', '李四', '13900139000', '0')"
                );

                logger.info("jwgl_school_info表创建成功并插入测试数据");
            } else {
                logger.info("jwgl_school_info表已存在，跳过创建");
                
                // 检查是否需要添加current_semester字段
                boolean hasCurrentSemester = jdbcTemplate.queryForObject(
                    "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'zjcloudtest' AND table_name = 'jwgl_school_info' AND column_name = 'current_semester'",
                    Integer.class) > 0;
                
                if (!hasCurrentSemester) {
                    jdbcTemplate.execute("ALTER TABLE jwgl_school_info ADD COLUMN current_semester varchar(50) DEFAULT NULL");
                    logger.info("添加current_semester字段成功");
                }
            }
        } catch (Exception e) {
            logger.error("初始化jwgl_school_info表失败: {}", e.getMessage(), e);
        }
    }
}